        <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Secretario_model extends CI_Model{
       
       public function __construct()
        {
        $this->load->library('session');
        $this->load->database();
        }
       
	   
	   public function res1()
	   {
        $lu = $this->input->post('lugar');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
  	   	$sql = "select 
                  departamento,nombre_cam, id_expediente
                from
                  expediente e inner join camara c on e.id_camara = c.id_camara
                               inner join ubicacion u on u.id_ubicacion = c.id_ubicacion
                where
                  lower(u.departamento) like lower('$lu')";
          $variable = $this->db->query($sql);
        return $variable->result();
	   }

     public function res11()
     {
        $lu = $this->input->post('lugar');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sql = "select 
                  nombre_cam,nombre,fecha_resol, id_expediente
                from
                  expediente e inner join camara c on e.id_camara = c.id_camara
                               inner join competencia p on c.id_competencia = p.id_competencia
                where
                  lower(p.nombre) like lower('%$lu%') and fecha_resol between '$fini' and '$ffin'";
          $variable = $this->db->query($sql);
        return $variable->result();
     }
   
     public function res2()
     {
        $tp_camara=$this->input->post('camara');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sql="select
                id_expediente,nombre_estado_juri, nombre_estado_admin
              from
                expediente e inner join camara c on e.id_camara = c.id_camara
                       inner join estadojuridico ej on e.id_estado_juri = ej.id_estado_juri
                       inner join estadoadmin ea on e.id_estado_admin = ea.id_estado_admin
                       inner join competencia p on p.id_camara = c.id_camara
              where
                tipo_camara=$tp_camara and fecha_ingreso between '$fini' and '$ffin'";
        $variable = $this->db->query($sql);
        return $variable->result();
      }

      public function res21()
     {
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sql="select
                nombre_cam, count(*)
              from
                expediente e inner join camara c on e.id_camara = c.id_camara
                       inner join estadojuridico ej on e.id_estado_juri = ej.id_estado_juri
                       inner join estadoadmin ea on e.id_estado_admin = ea.id_estado_admin
              where
                fecha_ingreso between '$fini' and '$ffin'
              group by
                nombre_cam
              order by
                1";
        $variable = $this->db->query($sql);
        return $variable->result();
      }

      public function res3()
     {
        $tp_camara=$this->input->post('camara');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sql="select
                nombre_cam,fecha_resol,count(nombre),detalle
              from
                competencia p inner join camara c on p.id_competencia=c.id_camara
              where
                nombre = '$tp_camara'  and fecha_resol between '$fini' and '$ffin'
              group by
                nombre_cam,fecha_resol,detalle";
        $variable = $this->db->query($sql);
        return $variable->result();
      }

      public function res4()
     {
        $zona=$this->input->post('zona');
        $ap_materia=$this->input->post('materia');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sql="select
                departamento,municipio,p.detalle,nombre_cam,fecha_resol,count(*)
              from
                competencia p inner join camara c on p.id_camara = c.id_camara
                        inner join ubicacion u on c.id_ubicacion = u.id_ubicacion
              where
                zona = '$zona' and nombre = '$ap_materia' and fecha_resol between '$fini' and '$ffin'
              group by
                departamento,municipio,p.detalle,nombre_cam,fecha_resol";
        $variable = $this->db->query($sql);
        return $variable->result();
      }

      public function res5()
     {
        $tp_apela=$this->input->post('tipo_ap');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sqlIngreso= "select
                        extract(year from fecha_ingreso),count(extract(year from fecha_ingreso))
                      from
                        expediente e1
                      where
                        extract(year from fecha_ingreso) is not null and fecha_ingreso between '$fini' and '$ffin' and tipo_apelacion=$tp_apela
                      group by
                        extract(year from fecha_ingreso)
                      order by 1";
        $variable = $this->db->query($sqlIngreso);
        return $variable->result();
      }

      public function res51()
     {
        $tp_apela=$this->input->post('tipo_ap');
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sqlEgreso = "select
                        extract(year from fecha_fin),count(extract(year from fecha_fin))
                      from
                        expediente e2
                      where
                        extract(year from fecha_fin) is not null and fecha_fin between '$fini' and '$ffin' and tipo_apelacion=$tp_apela
                      group by
                        extract(year from fecha_fin)
                      order by 1";
        $variable = $this->db->query($sqlEgreso);
        return $variable->result();
      }

      public function res6()
     {
        $fini = $this->input->post('fini');
        $ffin = $this->input->post('ffin');
        $sql="select
                count(*),
                (select
                  case when tipo_apelacion=1 then 'AP-Civil'
                       when tipo_apelacion=2 then 'AP-Laboral'
                       when tipo_apelacion=3 then 'AP-Mercantil'
                  end
                ),nombre_cam,municipio,departamento,zona
              from
                expediente e inner join camara c on e.id_camara = c.id_camara
                       inner join ubicacion u on c.id_ubicacion = u.id_ubicacion
              where
                fecha_ingreso between '$fini' and '$ffin'
              group by
                tipo_apelacion,nombre_cam,municipio,departamento,zona
              order by
                6 desc";
        $variable = $this->db->query($sql);
        return $variable->result();
      }
    }
